package com.gome.gop.util.export;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.xmind.core.ITopic;

/**
 * @Title:
 * @Description:
 * @Author:chengcheng7
 * @Since: 2022/10/4 15:42
 * @Copyright:Copyright (c) 2018 - 2100
 * @Company:gome
 * @Version:1.1.0
 */
public class ExportFile {
    
    private HttpServletRequest request;
    
    private HttpServletResponse response;
    
    private String name;
    
    private List<ExportSheet> exportSheetList = new ArrayList<>();
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public List<ExportSheet> getExportSheetList() {
        return exportSheetList;
    }
    
    public void setExportSheetList(List<ExportSheet> exportSheetList) {
        this.exportSheetList = exportSheetList;
    }
    
    public HttpServletRequest getRequest() {
        return request;
    }
    
    public void setRequest(HttpServletRequest request) {
        this.request = request;
    }
    
    public HttpServletResponse getResponse() {
        return response;
    }
    
    public void setResponse(HttpServletResponse response) {
        this.response = response;
    }
    
    public void buildPost() {
        
        OutputStream outputStream = null;
        try {
            setExcelDownloadHeader(name, request, this.response);
            outputStream = new FileOutputStream(name);
            export(exportSheetList, outputStream);
            this.response.flushBuffer();
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                outputStream.close();
            }
            catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }
    
    public void build() {
        
        ServletOutputStream outputStream = null;
        try {
            setExcelDownloadHeader(name + ".xls", request, this.response);
            outputStream = response.getOutputStream();
            export(exportSheetList, outputStream);
            this.response.flushBuffer();
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                outputStream.close();
            }
            catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }
    
    public void buildXmind() {
        
        ServletOutputStream outputStream = null;
        try {
            setExcelDownloadHeader(name + ".xmind", request, this.response);
            outputStream = response.getOutputStream();
            XmindUtils.export(this, outputStream);
            this.response.flushBuffer();
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                outputStream.close();
            }
            catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }
    
    private void export(List<ExportSheet> exportSheetList, OutputStream outputStream)
        throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        for (ExportSheet exportSheet : this.exportSheetList) {
            Sheet sheet = workbook.createSheet(exportSheet.getName());
            setColumnWidth(sheet, exportSheet);
            if (CollectionUtils.isEmpty(exportSheet.getExportLines())) {
                continue;
            }
            createRow(sheet, exportSheet);
        }
        resetStyle(workbook);
        workbook.write(outputStream);
    }
    
    private HSSFCellStyle style1 = null;
    
    private HSSFCellStyle style2 = null;
    
    private void resetStyle(HSSFWorkbook workbook) {
        
        newStyle(workbook);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);
            resetStyle(sheet);
        }
        
    }
    
    private void newStyle(HSSFWorkbook workbook) {
        resetStyle1(workbook);
        resetStyle2(workbook);
    }
    
    private void resetStyle1(HSSFWorkbook workbook) {
        style1 = workbook.createCellStyle();
        style1.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 字体颜色
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        style1.setFont(font);
        
    }
    
    private void resetStyle2(HSSFWorkbook workbook) {
        style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 字体颜色
        Font font = workbook.createFont();
        font.setColor(IndexedColors.RED.getIndex());
        style2.setFont(font);
        
    }
    
    private void resetStyle(HSSFSheet sheet) {
        sheet.createFreezePane(2, 0);
        resetCellStyle(sheet);
        mergeRegion(sheet, null);
        // mergeRegion(sheet, new Region(0, sheet.getLastRowNum(), 0, 0));
    }
    
    public static void main(String[] args)
        throws IOException {
        ExportFile exportFile = new ExportFile();
        FileInputStream inputStream = new FileInputStream("D:\\代码解析.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        // sheet.createFreezePane(2, 0);
        // exportFile.mergeRegion(sheet, null);
        exportFile.resetStyle(sheet);
        OutputStream outputStream = new FileOutputStream("D:\\代码解析333.xls");
        workbook.write(outputStream);
        
    }
    
    private void mergeRegion(HSSFSheet sheet, Region regionPrevious) {
        
        Integer lineStart = null;
        Integer lineEnd = null;
        Integer column = null;
        if (regionPrevious == null) {
            lineStart = 0;
            lineEnd = sheet.getLastRowNum();
            column = 2;
        }
        if (regionPrevious != null) {
            lineStart = regionPrevious.getRowStart();
            lineEnd = regionPrevious.getRowEnd();
            column = regionPrevious.getColumnEnd() + 2;
        }
        for (Integer lineNo = lineStart; lineNo <= lineEnd; lineNo++) {
            String current = obtainCellValue(sheet, lineNo, column);
            String below = obtainCellValue(sheet, lineNo + 1, column);
            if (StringUtils.isBlank(current)) {
                doMergeRegion(sheet, regionPrevious);
                continue;
            }
            // 合并前边,向右推进
            if (lineNo.equals(lineEnd) || !StringUtils.equals(current, below)) {
                // 向右推进
                Region region = new Region(lineStart, lineNo, column, column);
                forwardToRight(sheet, regionPrevious, region);
                lineStart = lineNo + 1;
            }
        }
        
    }
    
    private void forwardToRight(HSSFSheet sheet, Region regionPrevious, Region region) {
        
        // 与前边的行一样,与前边合并向右推进
        if (regionPrevious != null && region != null && regionPrevious.getRowStart().equals(region.getRowStart())
            
            && regionPrevious.getRowEnd().equals(region.getRowEnd())) {
            
            region.setColumnStart(regionPrevious.getColumnStart());
            mergeRegion(sheet, region);
            return;
        }
        // 与前边不一样,合并前边
        doMergeRegion(sheet, regionPrevious);
        // 与前边不一样,新的项目向前推进
        mergeRegion(sheet, region);
        
    }
    
    private void doMergeRegion(HSSFSheet sheet, Region region) {
        if (region == null || region.getMerged()) {
            return;
        }
        if (region.getRowEnd().equals(region.getRowStart()) && region.getColumnEnd().equals(region.getColumnStart())) {
            return;
        }
        StringBuilder stringBuilder = new StringBuilder();
        Integer columnEnd = region.getColumnEnd() + 1;
        for (Integer column = region.getColumnStart(); column <= columnEnd; column++) {
            if (column % 2 == 0) {
                append(stringBuilder, obtainCellValue(sheet, region.getRowStart(), column));
                continue;
            }
            for (java.lang.Integer row = region.rowStart; row <= region.getRowEnd(); row++) {
                append(stringBuilder, obtainCellValue(sheet, row, column));
            }
        }
        HSSFCell cell = obtainCell(sheet, region.getRowStart(), region.getColumnStart());
        resetCellStyle(cell);
        cell.setCellValue(new HSSFRichTextString(stringBuilder.toString().trim()));
        sheet.addMergedRegion(new CellRangeAddress(region.getRowStart(), region.getRowEnd(), region.getColumnStart(), region.getColumnEnd()));
        region.setMerged(true);
    }
    
    private void resetCellStyle(HSSFCell cell) {
        
        CellStyle cellStyle = cell.getSheet().getWorkbook().createCellStyle();
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);
        
    }
    
    private void append(StringBuilder stringBuilder, String obtainCellValue) {
        
        if (StringUtils.isNotBlank(obtainCellValue)) {
            stringBuilder.append(obtainCellValue);
            stringBuilder.append("\r\n");
        }
        
    }
    
    static class Region {
        
        private Boolean merged;
        
        private Integer rowStart;
        
        private Integer rowEnd;
        
        private Integer columnStart;
        
        private Integer columnEnd;
        
        public Boolean getMerged() {
            return merged;
        }
        
        public void setMerged(Boolean merged) {
            this.merged = merged;
        }
        
        public Region(Integer rowStart, Integer rowEnd, Integer columnStart, Integer columnEnd) {
            this.rowStart = rowStart;
            this.rowEnd = rowEnd;
            this.columnStart = columnStart;
            this.columnEnd = columnEnd;
            merged = false;
        }
        
        public static Integer newColumnStart(Region region) {
            
            if (region == null) {
                return 2;
            }
            return region.getColumnEnd() + 2;
            
        }
        
        public static Integer newColumnEnd(HSSFSheet sheet) {
            
            return Integer.valueOf(obtainMaxRowLength(sheet));
            
        }
        
        public static Integer newRowStart(Region region) {
            
            if (region == null) {
                return 0;
            }
            return region.getRowStart();
            
        }
        
        public static Integer newRowEnd(Region region, HSSFSheet sheet) {
            
            if (region == null) {
                return sheet.getPhysicalNumberOfRows() - 1;
            }
            return region.getRowEnd();
            
        }
        
        public Integer getRowStart() {
            return rowStart;
        }
        
        public void setRowStart(Integer rowStart) {
            this.rowStart = rowStart;
        }
        
        public Integer getRowEnd() {
            return rowEnd;
        }
        
        public void setRowEnd(Integer rowEnd) {
            this.rowEnd = rowEnd;
        }
        
        public Integer getColumnStart() {
            return columnStart;
        }
        
        public void setColumnStart(Integer columnStart) {
            this.columnStart = columnStart;
        }
        
        public Integer getColumnEnd() {
            return columnEnd;
        }
        
        public void setColumnEnd(Integer columnEnd) {
            this.columnEnd = columnEnd;
        }
        
        @Override
        public String toString() {
            return "Region{" + "rowStart=" + rowStart + ", rowEnd=" + rowEnd + ", columnStart=" + columnStart + ", columnEnd=" + columnEnd + '}';
        }
    }
    
    private void resetCellStyle(HSSFSheet sheet) {
        
        short maxLength = obtainMaxRowLength(sheet);
        
        for (int column = 0; column < maxLength; column++) {
            
            for (int line = 0; line < sheet.getPhysicalNumberOfRows() - 1; line++) {
                resetCellStyle(sheet, column, line);
            }
            
        }
        
    }
    
    private static short obtainMaxRowLength(HSSFSheet sheet) {
        
        short maxLength = 0;
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (maxLength < row.getLastCellNum()) {
                maxLength = row.getLastCellNum();
            }
        }
        return maxLength;
        
    }
    
    private void resetCellStyle(HSSFSheet sheet, int column, int line) {
        
        // 当前
        HSSFCell currentCell = obtainCell(sheet, line, column);
        // 上边一个
        HSSFCell upperCell = obtainCell(sheet, line - 1, column);
        // 下边一个
        HSSFCell belowCell = obtainCell(sheet, line + 1, column);
        // 与一个相同,则染色当前和下一个(颜色与上一个不同)
        if (StringUtils.isBlank(obtainCellValue(currentCell))) {
            return;
        }
        if (StringUtils.equals(obtainCellValue(currentCell), obtainCellValue(belowCell))) {
            HSSFCellStyle cellStyle = obtainCellStyle(upperCell, currentCell);
            currentCell.setCellStyle(cellStyle);
            belowCell.setCellStyle(cellStyle);
        }
        
    }
    
    private HSSFCellStyle obtainCellStyle(HSSFCell upperCell, HSSFCell currentCell) {
        
        HSSFCellStyle currentCellCellStyle = currentCell.getCellStyle();
        if (currentCellCellStyle.equals(style1) || currentCellCellStyle.equals(style2)) {
            return currentCellCellStyle;
        }
        return obtainDifferentCellStyle(upperCell);
        
    }
    
    private HSSFCellStyle obtainDifferentCellStyle(HSSFCell cell) {
        
        return obtainDifferentCellStyle(obtainCellStyle(cell));
        
    }
    
    private HSSFCellStyle obtainCellStyle(HSSFCell cell) {
        
        if (cell == null) {
            return null;
        }
        return cell.getCellStyle();
        
    }
    
    private String obtainCellValue(HSSFCell cell) {
        
        if (cell == null) {
            return null;
        }
        return cell.getStringCellValue();
        
    }
    
    private HSSFCellStyle obtainDifferentCellStyle(HSSFCellStyle cellStyle) {
        
        if (cellStyle == null || cellStyle.equals(style2)) {
            return style1;
        }
        return style2;
        
    }
    
    private String obtainCellValue(HSSFSheet sheet, int line, int column) {
        HSSFCell cell = obtainCell(sheet, line, column);
        if (cell != null) {
            return cell.getStringCellValue();
        }
        return null;
    }
    
    private HSSFCell obtainCell(HSSFSheet sheet, int line, int column) {
        
        if (notExistCell(sheet, line, column)) {
            return null;
        }
        return sheet.getRow(line).getCell(column);
    }
    
    private HSSFCellStyle obtainCellStyle(HSSFSheet sheet, int lineNo, int columnNo) {
        
        if (existCell(sheet, lineNo, columnNo)) {
            return null;
        }
        return sheet.getRow(lineNo).getCell(columnNo).getCellStyle();
        
    }
    
    private boolean notExistCell(HSSFSheet sheet, int lineNo, int columnNo) {
        return !existCell(sheet, lineNo, columnNo);
    }
    
    private boolean existCell(HSSFSheet sheet, int lineNo, int columnNo) {
        
        if (lineNo >= sheet.getPhysicalNumberOfRows() || lineNo < 0) {
            return false;
        }
        if (columnNo > sheet.getRow(lineNo).getLastCellNum()) {
            return false;
        }
        return true;
        
    }
    
    private void setColumnWidth(Sheet sheet, ExportSheet exportSheet) {
        
        Integer maxLength = 0;
        for (int i = 0; i < exportSheet.getExportLines().size(); i++) {
            List<String> row = exportSheet.getExportLines().get(i);
            maxLength = maxLength > row.size() ? maxLength : row.size();
        }
        Integer[] lengthList = new Integer[maxLength];
        for (int i = 0; i < exportSheet.getExportLines().size(); i++) {
            List<String> row = exportSheet.getExportLines().get(i);
            addLengthList(lengthList, row);
        }
        setColumnWidth(sheet, lengthList);
        
    }
    
    private void setColumnWidth(Sheet sheet, Integer[] lengthList) {
        for (int i = 0; i < lengthList.length; i++) {
            sheet.setColumnWidth(i, 238 * lengthList[i]);
        }
    }
    
    private void addLengthList(Integer[] lengthList, List<String> row) {
        
        for (int i = 0; i < lengthList.length && i < row.size(); i++) {
            if (lengthList[i] == null || lengthList[i] < row.get(i).length()) {
                lengthList[i] = row.get(i).length();
            }
        }
        
    }
    
    private void createRow(Sheet sheet, ExportSheet exportSheet) {
        for (int i = 0; i < exportSheet.getExportLines().size(); i++) {
            Row row = sheet.createRow(i);
            createColumn(row, exportSheet.getExportLines().get(i));
        }
    }
    
    private void createColumn(Row row, List<String> strings) {
        
        for (int i = 0; i < strings.size(); i++) {
            Cell cell = row.createCell(i);
            if (StringUtils.isNotBlank(strings.get(i))) {
                cell.setCellValue(strings.get(i));
            }
        }
        
    }
    
    private void setExcelDownloadHeader(String fileName, HttpServletRequest request, HttpServletResponse response)
        throws UnsupportedEncodingException {
        
        String agent = (String)request.getHeader("USER-AGENT");
        if (agent != null && agent.indexOf("Firefox") != -1) {
            // 火狐浏览器特殊处理
            fileName = "=?UTF-8?B?" + (new String(Base64.encodeBase64(fileName.getBytes("UTF-8")))) + "?=";
        }
        else {
            fileName = URLEncoder.encode(fileName, "UTF8");
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\";target=_blank");
    }
}
